package upcweb.dao;

import java.net.ConnectException;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;

import com.mysql.jdbc.Connection;

import upcweb.util.ClsReturn;

public class SQLProcDAO {

	public ClsReturn execQuery(String sql_string,Object[] args) {
		return execProc(sql_string, args);
	}
	
	public void cerrarCOnexion(CallableStatement cs) throws SQLException {
		cs.close();
	}
	
	public ClsReturn execProc(String sql_string,Object[] args)  {
		ClsReturn ret = new ClsReturn();
		Connection oCnn = null;
		
		
		try {			
			oCnn = (Connection) ConexionDAO.newConexion();
			CallableStatement cs = oCnn.prepareCall(sql_string);
			int i = 0;
			for(Object parm : args){
			i+=1;
			cs.setObject(i,parm);
			ret.getInValues().add(parm.toString());
			}		
			cs.execute();
			ret.setRows(cs.getUpdateCount());
			if (ret.getRows()>0){
				ret.setFlag(true);
			}
			cerrarCOnexion(cs);
			oCnn.close();
			return ret;
		} catch (SQLException e) {
			// TODO: handle exception
			return new ClsReturn(e);
		} finally{
			
		}
	}
	
	public String[] execProc(String sql_string,Object[] args,int out_parms) {
		String[] val_out = new String[]{};
		Connection oCnn = null;
		try {			
			
			oCnn = (Connection) ConexionDAO.newConexion();
			
			CallableStatement cs = oCnn.prepareCall(sql_string);
			int i = 0;
			for(Object parm : args){
				i+=1;
				cs.setObject(i,parm);	
			}
			for(int x=i+1;x<i+out_parms;x++){
				cs.registerOutParameter(x,Types.OTHER);
			}
				cs.execute();
			for(int x=i+1;x<i+out_parms;x++){
				val_out[x-i+1] = cs.getString(x);
			}
			cerrarCOnexion(cs);	
			oCnn.close();
		} catch (SQLException e) {
			// TODO: handle exception
			System.err.println(e.getErrorCode()+ " : " + e.getMessage());
			
		} finally{
		}
		return val_out;
	}
	
	public ClsReturn execQuery(String sql_string,Object[] args,int out_parms) {
		ClsReturn ret = new ClsReturn();
		//String[] val_out = new String[]{};
		//ArrayList<String> inValues= new ArrayList<String>();
		Connection oCnn = null;
		try {
			
			oCnn = (Connection) ConexionDAO.newConexion();
			
			CallableStatement cs = oCnn.prepareCall(sql_string);
			int i = 0;
			for(Object parm : args){
			i+=1;
			cs.setObject(i,parm);	
			//inValues.add(parm.toString());
			ret.getInValues().add(parm.toString());
			}
			//inValues.toArray(ret.getValues());
			for(int x=i+1;x<=i+out_parms;x++){
				cs.registerOutParameter(x,Types.OTHER);
			}
			cs.execute();
//			System.out.println(i);
//			System.out.println(out_parms);
			for(int x=i+1;x<=i+out_parms;x++){				
				//val_out[x-i+1] = cs.getString(x);
				ret.getValues().add(cs.getString(x));
			}
			//ret.setValues(val_out);
			//ps.execute();
			ret.setFlag(true);
			cerrarCOnexion(cs);
			oCnn.close();
			return ret;
		} catch (SQLException e) {			
			// TODO: handle exception
			//e.printStackTrace();
			ret = new ClsReturn(e);
		}finally{
			
		}
		return ret;
	}

	public ResultSet getData(String sql_string, Object[] args){
			Connection oCnn = null;
		try {
			oCnn = (Connection) ConexionDAO.newConexion();
			CallableStatement ps = oCnn.prepareCall(sql_string);
			int i = 0;
			for(Object parm : args){
			i+=1;
			ps.setObject(i,parm);	
			}			
			ResultSet rs = ps.executeQuery();
			//cerrarCOnexion(ps);
			//oCnn.close();
			return rs;
		} catch (SQLException e) {
			// TODO: handle exception
			System.err.println(e.getErrorCode()+ " : " + e.getMessage());
			return null;
		}finally{
			
			
		}
	}
}






















